package cn.mhome.merchant.dao.spring.data.repository;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import cn.mhome.merchant.dao.spring.data.repository.entity.EntityMap;
import cn.mhome.merchant.dao.spring.data.repository.entity.EntityMapper;
import cn.mhome.merchant.entity.common.result.Pager;
import cn.mhome.merchant.util.StringUtils;

/**
 * jpa dao支持的基础类
 *
 * @param <T>
 * @param <ID>
 * @author hanya
 */
// TODO ZHOUWEI 这个使用的方式需要考虑
public class BaseJpaDaoSupport<T extends Serializable, ID extends Serializable> {

	@PersistenceContext(unitName = "mplusPersistenceUnit")
	protected EntityManager entityManager;

	/**
	 * 执行分页查询的基础方法
	 *
	 * @param mainJpql
	 *            查询条件,所查询的对象别名必须为<strong>obj</strong>
	 * @param paramList
	 *            查询所需参数
	 * @param page
	 *            当前页
	 * @param pageSize
	 *            当前页码
	 * @return
	 */
	protected Pager executePageableQuery(String mainJpql, List<Object> paramList, int page, int pageSize) {

		Query totalQuery = this.entityManager
				.createQuery(new StringBuffer("select count(*) ").append(mainJpql).toString());
		Query dataQuery = this.entityManager.createQuery(new StringBuffer("select obj ").append(mainJpql).toString());
		dataQuery.setFirstResult((page - 1) * pageSize);
		dataQuery.setMaxResults(pageSize);
		for (int i = 0; i < paramList.size(); i++) {
			totalQuery.setParameter(i + 1, paramList.get(i));
			dataQuery.setParameter(i + 1, paramList.get(i));
		}
		Long total = (Long) totalQuery.getSingleResult();
		List<T> dataList = dataQuery.getResultList();
		Pager departmentPageVO = new Pager(page, pageSize);
		departmentPageVO.setData(dataList);
		departmentPageVO.setTotalResults(total.intValue());
		return departmentPageVO;
	}

	protected Pager executePageableQuery(String mainJpql, HashMap<String, Object> paramMap, int page, int pageSize) {

		Query totalQuery = this.entityManager
				.createQuery(new StringBuffer("select count(*) ").append(mainJpql).toString());
		Query dataQuery = this.entityManager.createQuery(new StringBuffer("select obj ").append(mainJpql).toString());
		dataQuery.setFirstResult((page - 1) * pageSize);
		dataQuery.setMaxResults(pageSize);
		Iterator iter = paramMap.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry entry = (Map.Entry) iter.next();
			String key = String.valueOf(entry.getKey());
			Object val = entry.getValue();
			totalQuery.setParameter(key, val);
			dataQuery.setParameter(key, val);
		}

		Long total = (Long) totalQuery.getSingleResult();
		List<T> dataList = dataQuery.getResultList();
		Pager departmentPageVO = new Pager(page, pageSize);
		departmentPageVO.setData(dataList);
		departmentPageVO.setTotalResults(total.intValue());
		return departmentPageVO;
	}

	/**
	 * 子类可以继承getSession方法
	 *
	 * @return
	 */
	protected Session getSession() {
		Session session = entityManager.unwrap(Session.class);
		return session;
	}

	/**
	 * 多表查询不分页
	 */
	public List<?> findBySqlForList(String sql, Map<String, Object> params, Class<?> clazz) {
		Session session = entityManager.unwrap(Session.class);
		org.hibernate.Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		if (params != null) {
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				if(entry.getValue() instanceof Collection<?>){
					query.setParameterList(entry.getKey(), (Collection<?>)entry.getValue());
				}else{
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
		}
		List<Map<Object, Object>> list = query.list();
		List<?> entitys = (List<?>) EntityMapper.mapperList(list, clazz);
		return entitys;
	}

	public Pager findBySqlForPage(String selectSql, String fromSql, Map<String, Object> params,
			Class<?> clazz, int page, int pagesize) {
		String countStr = "SELECT count(1) from (" + selectSql + fromSql + ") as asPro";
		return findBySqlForPage(selectSql, fromSql, countStr, params, clazz, page, pagesize);
	}

	/**
	 * 多表查询分页
	 */
	public Pager findBySqlForPage(String selectSql, String fromSql, String countSql, Map<String, Object> params,
			Class<?> clazz, int page, int pagesize) {
		Session session = entityManager.unwrap(Session.class);
		Pager pager = new Pager(page, pagesize);
		if (StringUtils.isBlank(countSql)) {
			countSql = "SELECT count(1) from (" + selectSql + fromSql + ") as asPro";
		}
		org.hibernate.Query pageQuery = session.createSQLQuery(countSql);
		org.hibernate.Query dataQuery = session.createSQLQuery(selectSql + fromSql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		dataQuery.setFirstResult(pager.getFirstResult());
		dataQuery.setMaxResults(pagesize);
		if (params != null) {
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				if(entry.getValue() instanceof Collection<?>){
					pageQuery.setParameterList(entry.getKey(),(Collection<?>)entry.getValue());
					dataQuery.setParameterList(entry.getKey(),(Collection<?>)entry.getValue());
				}else{
					pageQuery.setParameter(entry.getKey(),entry.getValue());
					dataQuery.setParameter(entry.getKey(),entry.getValue());
				}
			}
		}
		BigInteger count = (BigInteger) pageQuery.uniqueResult();
		if (count != null) {
			if (count.intValue() == 0) {
				pager.setTotalResults(0);
				return pager;
			}
			pager.setTotalResults(count.intValue());
		}

		List<Map<Object, Object>> list = dataQuery.list();
		List<?> entitys = (List<?>) EntityMapper.mapperList(list, clazz);
		pager.setData(entitys);
		return pager;
	}

	/**
	 * 多表查询分页
	 */
	public Pager findBySqlForPage(String selectSql, String orderBySql, Map<String, Object> params,
			Class<? extends EntityMap> clazz, int page, int pagesize, boolean DISTINCT, String columnStr) {
		Session session = entityManager.unwrap(Session.class);
		Pager pager = new Pager(page, pagesize);

		String countStr = "SELECT count(1) from ";
		if (DISTINCT) {
			countStr = "SELECT count(DISTINCT " + columnStr + ") from";
		}

		// 2015.6.24 sql里有group by 统计数量有问题
		org.hibernate.Query pageQuery = session.createSQLQuery(countStr + "(" + selectSql + ") as asPro");
		org.hibernate.Query dataQuery = session.createSQLQuery(selectSql + orderBySql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		dataQuery.setFirstResult(pager.getFirstResult());
		dataQuery.setMaxResults(pagesize);
		for (Map.Entry<String, Object> entry : params.entrySet()) {
			pageQuery.setParameter(entry.getKey(), entry.getValue());
			dataQuery.setParameter(entry.getKey(), entry.getValue());
		}

		BigInteger count = (BigInteger) pageQuery.uniqueResult();
		if (count != null) {
			if (count.intValue() == 0) {
				pager.setTotalResults(0);
				return pager;
			}
			pager.setTotalResults(count.intValue());
		}

		List<Map<Object, Object>> list = dataQuery.list();
		List<?> entitys = (List<?>) EntityMapper.mapperList(list, clazz);
		pager.setData(entitys);
		return pager;
	}

	/**
	 * 支持单表分页 必须使用hql
	 */
	public Pager findByHqlForPage(String Hql, Map<String, Object> params, int page, int pagesize) {

		Pager pager = new Pager(page, pagesize);

		String countStr = "SELECT count(1) ";
		Query totalQuery = this.entityManager.createQuery(countStr + Hql);
		Query dataQuery = this.entityManager.createQuery(Hql);
		dataQuery.setFirstResult(pager.getFirstResult());
		dataQuery.setMaxResults(pagesize);
		for (Map.Entry<String, Object> entry : params.entrySet()) {
			totalQuery.setParameter(entry.getKey(), entry.getValue());
			dataQuery.setParameter(entry.getKey(), entry.getValue());
		}
		Long total = (Long) totalQuery.getSingleResult();
		List<T> dataList = dataQuery.getResultList();
		pager.setData(dataList);
		pager.setTotalResults(total.intValue());
		return pager;
	}

	public List<T> findByHqlForList(String Hql, Map<String, Object> params) {
		Query dataQuery = this.entityManager.createQuery(Hql);
		for (Map.Entry<String, Object> entry : params.entrySet()) {
			dataQuery.setParameter(entry.getKey(), entry.getValue());
		}
		return dataQuery.getResultList();
	}

	/**
	 * 根据ID查询对象 带锁的 只支持单表
	 *
	 * @param findParam
	 * @return
	 */
	public Object findForUpdate(LockParam lockParam) {
		Object obj = null;
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT * FROM ").append(lockParam.getTableName()).append(" WHERE 1=1 ");

		if (lockParam.getColumnMap().size() > 0) {
			for (Map.Entry<String, Object> entry : lockParam.getColumnMap().entrySet()) {
				String columnName = entry.getKey();
				sqlBuffer.append("AND " + columnName + " =:" + columnName + " ");
			}
		}
		if (lockParam.isLock()) {
			sqlBuffer.append("FOR UPDATE");
		}
		Query query = entityManager.createNativeQuery(sqlBuffer.toString(), lockParam.getReturnClass());
		for (Map.Entry<String, Object> entry : lockParam.getColumnMap().entrySet()) {
			String columnName = entry.getKey();
			Object columnValue = entry.getValue();
			query.setParameter(columnName, columnValue);
		}

		List<?> result = query.getResultList();
		if (result != null && result.size() > 0) {
			obj = result.get(0);
		}
		return obj;
	}

	public void saveSelf(List<T> list, int count) {
		int i = 0;
		count = count == 0 ? 500 : count;
		for (T t : list) {
			entityManager.persist(t);
			if (i % count == 0) {
				entityManager.flush();
			}
			i++;
		}
		entityManager.flush();
	}
	
	/**
	 * 批量提交
	 * @param batchParam 
	 * @return
	 */
	public int batchInsert(InsertBatchParam batchParam) {
		if (StringUtils.isBlank(batchParam.getInsertTemplate()) ||
				batchParam.getValueSqlList().size() == 0)
			return 0;
		
		StringBuilder sql = new StringBuilder("");
		int iSize = 0,batchSize = batchParam.getBatchSize();
		int listLen = batchParam.getValueSqlList().size();
		String sqlTemplate = batchParam.getInsertTemplate();
		if(listLen < batchSize){
			for(String valueSql : batchParam.getValueSqlList()){
				iSize++;
				sql.append(valueSql);
				if(iSize==listLen){
					sql.append(";");
				}else{
					sql.append(",");
				}
			}
			String finalSql = sqlTemplate + sql.toString();
			Query query = entityManager.createNativeQuery(finalSql);
			query.executeUpdate();
		}else{
			for(String valueSql : batchParam.getValueSqlList()){
				iSize++;
				sql.append(valueSql);
				if(iSize <= (batchSize - 1)){
					sql.append(",");
				}
				if (iSize == batchSize) {
					sql.append(";");
					String finalSql = sqlTemplate + sql.toString();
					Query query = entityManager.createNativeQuery(finalSql);
					query.executeUpdate();
					iSize = 0;
					sql = new StringBuilder();
				}
			}
			if(listLen % batchSize != 0){
				String finalSql="";
				//这种情况是 excel表格里的数据比批量插入数据的整数倍还多，最后还要把剩余的记录添加进去
				//批次数量500条， 表格数据520条，那么最后20条是需要这个执行的
				if(sql.toString().endsWith(",")){
					finalSql = sqlTemplate + sql.toString().substring(0,sql.toString().length()-1)+";";
				}else{
					finalSql = sqlTemplate + sql.append(";").toString();
				}
				Query query = entityManager.createNativeQuery(finalSql);
				query.executeUpdate();
			}
		}
		return 0;
	}
}
